Release 10.1A: OpenEdge Data Management:
SQL Development

Working with triggers

The following limitations apply in the use of SQL and 4GL triggers:

SQL clients and servers do not execute 4GL-based triggers.
4GL clients and servers do not execute SQL Java triggers.
If both SQL and 4GL access are needed, you must have two triggers.
4GL does not observe SQL constraints.

Progress 4GL possesses a rich set of trigger mechanisms. Progress 4GL triggers are procedures that are written by the 4GL user, and they fire when record-oriented events such as creating, deleting, writing, or finding a record occur. 4GL triggers are associated with their corresponding database records using the 4GL Data Dictionary and execute in the context of a 4GL AppServer or a 4GL client.

SQL also possesses a rich set of trigger mechanisms. SQL triggers are Java procedures written by the SQL database administrator, and they fire when record-oriented or statement-oriented events, such as inserting, deleting, or updating data, occur. SQL triggers are defined for their corresponding database tables using the CREATE TRIGGER command. SQL triggers execute in the SQL server.

The SQL and 4GL trigger mechanisms are parallel but separate. The 4GL does not execute SQL triggers, and SQL does not execute 4GL triggers. This is because the two types of triggers require different execution engines: SQL requires the SQL engine, and the 4GL requires the 4GL engine.

Usually, a 4GL trigger will contain significant database integrity enforcement logic, or perhaps enforcement of business logic rules. If a database can also be updated by SQL users using SQL statements, the database administrator will want to apply the same enforcement rules when SQL updates occur. To do so, the data-specific logic should be replicated from the 4GL trigger to a SQL Java trigger. That new trigger can then be added to the database, using the CREATE TRIGGER statement. SQL will then use that trigger to enforce the defined logical rules and actions.

For more information on using Progress 4GL triggers, see OpenEdge Development: Progress 4GL Handbook . For more information on SQL triggers, see Chapter 9, " Stored Procedures and Triggers."